# coding=utf8

import pymssql
from tkinter import ttk
import tkinter as tk
import tkinter.font as tkFont
from tkinter import *
import tkinter.messagebox as messagebox  # 弹窗
# 打包的时候会用到（十进制的一个库）
import decimal
import main


class StudentPage1:
    def __init__(self, parent_window):
        parent_window.destroy()  # 销毁上一个界面
        self.window = tk.Tk()  # 初始框的声明
        self.window.title('学生登陆页面')
        self.window.geometry('1000x500+500+100')
        self.window.config(background="#B0E0E6")
        # 创建画布，这里可以存放照片等组件
        canvas = tk.Canvas(self.window, height=800, width=400)
        image_file = tk.PhotoImage(file='103245.png')
        image = canvas.create_image(10, 10, anchor='nw', image=image_file)  # 前两个参数为画布得坐标，anchor=nw则是把图片的左上角作为锚定点
        canvas.pack(side='top')  # 使用pack将画布进行简单得布局，放到了上半部分

        # 创建提示信息
        tk.Label(self.window, text='学生账号: ').place(x=400, y=250)
        tk.Label(self.window, text='登陆密码: ').place(x=400, y=290)

        self.admin_username = tk.Entry(self.window)
        self.admin_username.place(x=460, y=250)
        self.admin_pass = tk.Entry(self.window, show='*')
        self.admin_pass.place(x=460, y=290)
        # 登陆和返回首页得按钮
        btn_login = tk.Button(self.window, text='登陆', width=10, command=self.login)
        btn_login.place(x=400, y=330)
        btn_back = Button(self.window, text="返回首页", width=8, font=tkFont.Font(size=12), command=self.back)
        btn_back.place(x=500, y=330)
        self.window.mainloop()

    # 登陆的函数
    def login(self):
        # 数据库操作 查询管理员表
        db = pymssql.connect('LAPTOP-6IF4V507', 'adminmanage', '123456', '学生宿舍管理系统')  # 服务器名,账户,密码,数据库名
        cursor = db.cursor()  # 使用cursor()方法获取操作游标
        sql = "SELECT * FROM s_admin where student_id = '%s'" % (
            self.admin_username.get())  # 这里得user_name即为admin_id，这里是输入的用户名
        try:
            # 执行SQL语句
            cursor.execute(sql)
            # 获取所有记录列表，这里是返回的二元元组，如(('id','title'),('id','title'))
            results = cursor.fetchall()
            for row in results:
                student_id = row[0]
                student_pass = row[1]
                # 打印结果
                print("学生账号为：%s, \n\n学生密码为：%s" % (student_id, student_pass))
        except:
            print("Error: unable to fecth data")
            messagebox.showinfo('警告！', '用户名或密码不正确！')
        db.close()  # 关闭数据库连接

        print("正在登陆学生信息界面.......")

        # 判断输入的账号密码与数据库中的信息是否一致a
        if self.admin_pass.get() == student_pass:
            All_admin1(self.window)  # 进入管理员子菜单操作界面
        else:
            messagebox.showinfo('警告！', '用户名或密码不正确！')

    # 使得系统点击关闭的x号上返回指定页面，而不是关闭
    def back(self):
        main.StartPage(self.window)  # 显示主窗口 销毁本窗口


# 管理员子菜单操作界面
class All_admin1:
    def __init__(self, parent_window):
        parent_window.destroy()  # 自定销毁上一个界面
        self.window = tk.Tk()  # 初始框的声明
        self.window.title('信息管理界面')
        self.window.geometry('1000x500+500+100')
        label = Label(self.window, text="请选择需要进行的操作", font=("Verdana", 20))
        label.pack(pady=100)  # pady=100 界面的长度

        Button(self.window, text="学生信息管理", font=tkFont.Font(size=16), width=30, height=2,
               command=lambda: AdminManage1(self.window),
               fg='white', bg='gray', activebackground='black', activeforeground='white').pack()
        Button(self.window, text="管理员信息管理", font=tkFont.Font(size=16), width=30, height=2,
               command=lambda: User_AdminManage1(self.window),
               fg='white', bg='gray', activebackground='black', activeforeground='white').pack()
        Button(self.window, text="核酸信息管理", font=tkFont.Font(size=16), width=30, height=2,
               command=lambda: Buy_AdminManage1(self.window),
               fg='white', bg='gray', activebackground='black', activeforeground='white').pack()

        self.window.protocol("WM_DELETE_WINDOW", self.back)  # 捕捉右上角关闭点击
        self.window.mainloop()  # 进入消息循环

    def back(self):
        main.StartPage(self.window)  # 显示主窗口 销毁本窗口


# 学生信息操作界面
# 学生信息操作界面
def updata_row():
    res = messagebox.askyesnocancel('警告！', '该用户无权进行更新操作！')


class AdminManage1:
    def __init__(self, parent_window):
        parent_window.destroy()  # 自动销毁上一个界面
        self.window = Tk()  # 初始框的声明
        self.window.title('管理员操作界面')
        self.window.geometry("730x800+300+30")  # 初始窗口在屏幕中的位置
        self.frame_left_top = tk.Frame(width=300, height=300)  # 指定框架，在窗口上可以显示，这里指定四个框架
        self.frame_right_top = tk.Frame(width=200, height=300)
        self.frame_center = tk.Frame(width=720, height=350)
        self.frame_bottom = tk.Frame(width=650, height=70)

        # 定义下方中心列表区域
        self.columns = ("学号", "姓名", "性别", "专业", '宿舍号', '入住时间')
        self.tree = ttk.Treeview(self.frame_center, show="headings", height=18, columns=self.columns)
        # 添加竖直滚动条
        self.vbar = ttk.Scrollbar(self.frame_center, orient=VERTICAL, command=self.tree.yview)
        # 定义树形结构与滚动条
        self.tree.configure(yscrollcommand=self.vbar.set)

        # 定义id1为修改id时的暂存变量，这个是为了更新信息而设计的
        self.id1 = 0

        # 表格的标题
        self.tree.column("学号", width=150, anchor='center')
        self.tree.column("姓名", width=150, anchor='center')
        self.tree.column("性别", width=100, anchor='center')
        self.tree.column("专业", width=100, anchor='center')
        self.tree.column("宿舍号", width=100, anchor='center')
        self.tree.column("入住时间", width=100, anchor='center')

        # grid方法将tree和vbar进行布局
        self.tree.grid(row=0, column=0, sticky=NSEW)
        self.vbar.grid(row=0, column=1, sticky=NS)

        # 定义几个数组，为中间的那个大表格做一些准备
        self.id = []
        self.name = []
        self.zye = []
        self.gender = []
        self.dno = []
        self.time = []

        # 打开数据库连接
        db = pymssql.connect('LAPTOP-6IF4V507', 'adminmanage', '123456', '学生宿舍管理系统')
        cursor = db.cursor()  # 使用cursor()方法获取操作游标
        sql = "SELECT  * FROM student"
        try:
            # 执行SQL语句
            cursor.execute(sql)
            # 获取所有记录列表
            results = cursor.fetchall()
            for row in results:
                self.id.append(row[0])
                self.name.append(row[1])
                self.gender.append(row[2])
                self.zye.append(row[3])
                self.time.append(row[4])
                self.dno.append(row[5])
        except:
            messagebox.showinfo('警告！', '数据库连接失败！')
        db.close()  # 关闭数据库连接

        print("test***********************")
        for i in range(min(len(self.id), len(self.name), len(self.zye), len(self.gender), len(self.dno))):  # 写入数据
            self.tree.insert('', i,
                             values=(self.id[i], self.name[i], self.gender[i], self.zye[i], self.dno[i], self.time[i],))

        for col in self.columns:  # 绑定函数，使表头可排序(这里的command=lambda _col=col还不是太懂)
            self.tree.heading(col, text=col, command=lambda _col=col: self.tree_sort_column(self.tree, _col, False))

        # 定义顶部区域
        # 定义左上方区域
        self.top_title = Label(self.frame_left_top, text="学生信息", font=('Verdana', 20))
        self.top_title.grid(row=0, column=0, columnspan=2, sticky=NSEW, padx=50, pady=10)  # NSEW表示允许组件向4个方向都可以拉伸

        # 定义下方区域
        self.chaxun = StringVar()
        self.right_bottom_gender_entry = Entry(self.frame_bottom, textvariable=self.chaxun, font=('Verdana', 15))
        self.right_bottom_button = ttk.Button(self.frame_bottom, text='学生id查询', width=20, command=self.put_data)
        self.right_bottom_button.grid(row=0, column=0, padx=20, pady=20)  # 位置设置
        self.right_bottom_gender_entry.grid(row=0, column=1)

        self.left_top_frame = tk.Frame(self.frame_left_top)
        self.var_id = StringVar()  # 声明学号
        self.var_name = StringVar()  # 声明姓名
        self.var_zhuanye = StringVar()
        self.var_gender = StringVar()  # 声明性别
        self.var_dno = StringVar()  # 声明宿舍号
        self.var_time = StringVar()
        # 商品id
        self.right_top_id_label = Label(self.frame_left_top, text="学生ID： ", font=('Verdana', 15))
        self.right_top_id_entry = Entry(self.frame_left_top, textvariable=self.var_id, font=('Verdana', 15))
        self.right_top_id_label.grid(row=1, column=0)
        self.right_top_id_entry.grid(row=1, column=1)
        # 商品名称
        self.right_top_name_label = Label(self.frame_left_top, text="学生姓名：", font=('Verdana', 15))
        self.right_top_name_entry = Entry(self.frame_left_top, textvariable=self.var_name, font=('Verdana', 15))
        self.right_top_name_label.grid(row=2, column=0)  # 位置设置
        self.right_top_name_entry.grid(row=2, column=1)
        # 商品价格
        self.right_top_gender_label = Label(self.frame_left_top, text="性别：", font=('Verdana', 15))
        self.right_top_gender_entry = Entry(self.frame_left_top, textvariable=self.var_gender, font=('Verdana', 15))
        self.right_top_gender_label.grid(row=3, column=0)  # 位置设置
        self.right_top_gender_entry.grid(row=3, column=1)
        # 销售数量
        self.right_top_gender_label = Label(self.frame_left_top, text="专业：", font=('Verdana', 15))
        self.right_top_gender_entry = Entry(self.frame_left_top, textvariable=self.var_zhuanye, font=('Verdana', 15))
        self.right_top_gender_label.grid(row=4, column=0)  # 位置设置
        self.right_top_gender_entry.grid(row=4, column=1)

        self.right_top_gender_label = Label(self.frame_left_top, text="入住时间：", font=('Verdana', 15))
        self.right_top_gender_entry = Entry(self.frame_left_top, textvariable=self.var_time, font=('Verdana', 15))
        self.right_top_gender_label.grid(row=6, column=0)  # 位置设置
        self.right_top_gender_entry.grid(row=6, column=1)

        self.right_top_gender_label = Label(self.frame_left_top, text="宿舍号：", font=('Verdana', 15))
        self.right_top_gender_entry = Entry(self.frame_left_top, textvariable=self.var_dno, font=('Verdana', 15))
        self.right_top_gender_label.grid(row=5, column=0)  # 位置设置
        self.right_top_gender_entry.grid(row=5, column=1)

        # 定义右上方区域
        self.right_top_title = Label(self.frame_right_top, text="操作：", font=('Verdana', 20))
        self.tree.bind('<Button-1>', self.click)  # 左键获取位置(tree.bind可以绑定一系列的事件，可以搜索ttk相关参数查看)
        self.right_top_button1 = ttk.Button(self.frame_right_top, text='新增学生信息', width=20, command=self.new_row)
        self.right_top_button2 = ttk.Button(self.frame_right_top, text='更新学生信息', width=20, command=updata_row)
        self.right_top_button3 = ttk.Button(self.frame_right_top, text='删除学生信息', width=20, command=self.del_row)

        # 定义下方区域，查询功能块
        self.chaxun = StringVar()
        self.right_bottom_gender_entry = Entry(self.frame_bottom, textvariable=self.chaxun, font=('Verdana', 15))
        self.right_bottom_button = ttk.Button(self.frame_bottom, text='学生姓名查询', width=20, command=self.put_data)
        self.right_bottom_button.grid(row=0, column=0, padx=30, pady=5)  # 位置设置
        self.right_bottom_gender_entry.grid(row=0, column=1)

        # 右上角按钮的位置设置
        self.right_top_title.grid(row=1, column=0, pady=10)
        self.right_top_button1.grid(row=2, column=0, padx=20, pady=10)
        self.right_top_button2.grid(row=3, column=0, padx=20, pady=10)
        self.right_top_button3.grid(row=4, column=0, padx=20, pady=10)

        # 整体区域定位，利用了Frame和grid进行布局
        self.frame_left_top.grid(row=0, column=0, padx=2, pady=5)
        self.frame_right_top.grid(row=0, column=1, padx=30, pady=30)
        self.frame_center.grid(row=1, column=0, columnspan=2, padx=4, pady=5)
        self.frame_bottom.grid(row=2, column=0, columnspan=2)

        # 设置固定组件，(0)就是将组件进行了固定
        self.frame_left_top.grid_propagate(0)
        self.frame_right_top.grid_propagate(0)
        self.frame_center.grid_propagate(0)
        self.frame_bottom.grid_propagate(0)

        self.frame_left_top.tkraise()  # 开始显示主菜单，tkraise()提高z轴的顺序（不太懂）
        self.frame_right_top.tkraise()  # 开始显示主菜单
        self.frame_center.tkraise()  # 开始显示主菜单
        self.frame_bottom.tkraise()  # 开始显示主菜单

        self.window.protocol("WM_DELETE_WINDOW", self.back)  # 捕捉右上角关闭点击，执行back方法
        self.window.mainloop()  # 进入消息循环

    # 将查到的信息放到中间的表格中
    def put_data(self):
        self.delButton()  # 先将表格内的内容全部清空

        # print(self.chaxun.get())	# 输入框内的内容
        # 打开数据库连接，准备查找指定的信息
        db = pymssql.connect('LAPTOP-6IF4V507', 'adminmanage', '123456', '学生宿舍管理系统')
        cursor = db.cursor()  # 使用cursor()方法获取操作游标
        sql = "SELECT * FROM student where sname = '%s'" % (self.chaxun.get())
        try:
            # 执行SQL语句
            cursor.execute(sql)
            # 获取所有记录列表
            results = cursor.fetchall()

            # 再次进行初始化，进行首行数据的插入
            self.id = []
            self.name = []
            self.gender = []
            self.dno = []
            self.zye = []
            self.time = []
            # 向表格中插入数据
            for row in results:
                self.id.append(row[0])
                self.name.append(row[1])
                self.gender.append(row[2])
                self.zye.append(row[3])
                self.time.append(row[4])
                self.dno.append(row[5])

        except:
            print("Error: unable to fetch data")
            messagebox.showinfo('警告！', '数据库连接失败！')
            db.close()  # 关闭数据库连接

        print("进行数据的插入")
        for i in range(min(len(self.id), len(self.time), len(self.zye), len(self.dno), len(self.name),
                           len(self.gender))):  # 写入数据
            self.tree.insert('', i,
                             values=(self.id[i], self.name[i], self.gender[i], self.zye[i], self.dno[i], self.time[i]))

        for col in self.columns:  # 绑定函数，使表头可排序
            self.tree.heading(col, text=col,
                              command=lambda _col=col: self.tree_sort_column(self.tree, _col, False))

    # 清空表格中的所有信息
    def delButton(self):
        x = self.tree.get_children()
        for item in x:
            self.tree.delete(item)

    # 在表格上的点击事件，这里是作用就是一点击表格就可以将信息直接写到左上角的框框中
    def click(self, event):
        self.col = self.tree.identify_column(event.x)  # 通过tree.identify_column()函数可以直接获取到列
        self.row = self.tree.identify_row(event.y)  # 行

        print(self.col)
        print(self.row)
        self.row_info = self.tree.item(self.row, "values")
        self.var_id.set(self.row_info[0])
        self.id1 = self.var_id.get()
        self.var_name.set(self.row_info[1])
        self.var_gender.set(self.row_info[2])
        self.var_zhuanye.set(self.row_info[3])
        self.var_dno.set(self.row_info[4])
        self.var_time.set(self.row_info[5])
        self.right_top_id_entry = Entry(self.frame_left_top, state='disabled', textvariable=self.var_id,
                                        font=('Verdana', 15))

    # 点击中间的表格的表头，可以将那一列进行排序
    def tree_sort_column(self, tv, col, reverse):  # Treeview、列名、排列方式
        l = [(tv.set(k, col), k) for k in tv.get_children('')]
        l.sort(reverse=reverse)  # 排序方式
        for index, (val, k) in enumerate(l):  # 根据排序后索引移动
            tv.move(k, '', index)
        tv.heading(col, command=lambda: self.tree_sort_column(tv, col, not reverse))  # 重写标题，使之成为再点倒序的标题

    def new_row(self):
        res = messagebox.askyesnocancel('警告！', '该用户无权进行添加操作！')

    # 删除行
    def del_row(self):
        res = messagebox.askyesnocancel('警告！', '该用户无权进行删除操作！')


    def back(self):
        All_admin1(self.window)  # 进入管理员子菜单操作界面


# 管理信息操作界面
class User_AdminManage1:
    def __init__(self, parent_window):
        parent_window.destroy()  # 销毁主界面

        self.window = Tk()  # 初始框的声明
        self.window.title('管理员操作界面')
        self.window.geometry("730x800+300+30")  # 初始窗口在屏幕中的位置
        self.frame_left_top = tk.Frame(width=300, height=300)  # 指定框架，在窗口上可以显示，这里指定四个框架
        self.frame_right_top = tk.Frame(width=200, height=300)
        self.frame_center = tk.Frame(width=720, height=350)
        self.frame_bottom = tk.Frame(width=650, height=70)

        self.id1 = 0

        # 定义下方中心列表区域
        self.columns = ("管理员号", "姓名", "性别", "年龄", '宿舍楼号')
        self.tree = ttk.Treeview(self.frame_center, show="headings", height=18, columns=self.columns)
        self.vbar = ttk.Scrollbar(self.frame_center, orient=VERTICAL, command=self.tree.yview)
        # 定义树形结构与滚动条
        self.tree.configure(yscrollcommand=self.vbar.set)

        # 表格的标题
        self.tree.column("管理员号", width=150, anchor='center')  # 表示列,不显示
        self.tree.column("姓名", width=150, anchor='center')
        self.tree.column("性别", width=100, anchor='center')
        self.tree.column("年龄", width=100, anchor='center')
        self.tree.column("宿舍楼号", width=100, anchor='center')

        # 调用方法获取表格内容插入
        self.tree.grid(row=0, column=0, sticky=NSEW)
        self.vbar.grid(row=0, column=1, sticky=NS)

        self.id = []
        self.name = []
        self.gender = []
        self.age = []
        self.bno = []
        # 打开数据库连接
        db = pymssql.connect('LAPTOP-6IF4V507', 'adminmanage', '123456', '学生宿舍管理系统')
        cursor = db.cursor()  # 使用cursor()方法获取操作游标
        sql = "SELECT * FROM admin"  # SQL 查询语句
        try:
            # 执行SQL语句
            cursor.execute(sql)
            # 获取所有记录列表
            results = cursor.fetchall()
            for row in results:
                self.id.append(row[0])
                self.name.append(row[1])
                self.gender.append(row[2])
                self.age.append(row[3])
                self.bno.append(row[4])
        except:
            print("Error: unable to fetch data")
            messagebox.showinfo('警告！', '数据库连接失败！')
        db.close()  # 关闭数据库连接

        print("test***********************")
        for i in range(min(len(self.id), len(self.name), len(self.gender), len(self.age))):  # 写入数据
            self.tree.insert('', i, values=(self.id[i], self.name[i], self.gender[i], self.age[i], self.bno[i]))

        for col in self.columns:  # 绑定函数，使表头可排序
            self.tree.heading(col, text=col,
                              command=lambda _col=col: self.tree_sort_column(self.tree, _col, False))

        # 定义顶部区域
        # 定义左上方区域
        self.top_title = Label(self.frame_left_top, text="管理员信息:", font=('Verdana', 20))
        self.top_title.grid(row=0, column=0, columnspan=2, sticky=NSEW, padx=50, pady=10)

        self.left_top_frame = tk.Frame(self.frame_left_top)
        self.var_id = StringVar()  # 声明学号
        self.var_name = StringVar()  # 声明姓名
        self.var_gender = StringVar()  # 声明性别
        self.var_age = StringVar()  # 声明年龄
        self.var_bno = StringVar()
        # 学号
        self.right_top_id_label = Label(self.frame_left_top, text="管理员号： ", font=('Verdana', 15))
        self.right_top_id_entry = Entry(self.frame_left_top, textvariable=self.var_id, font=('Verdana', 15))
        self.right_top_id_label.grid(row=1, column=0)  # 位置设置
        self.right_top_id_entry.grid(row=1, column=1)
        # 姓名
        self.right_top_name_label = Label(self.frame_left_top, text="姓名：", font=('Verdana', 15))
        self.right_top_name_entry = Entry(self.frame_left_top, textvariable=self.var_name, font=('Verdana', 15))
        self.right_top_name_label.grid(row=2, column=0)  # 位置设置
        self.right_top_name_entry.grid(row=2, column=1)
        # 性别
        self.right_top_gender_label = Label(self.frame_left_top, text="性别：", font=('Verdana', 15))
        self.right_top_gender_entry = Entry(self.frame_left_top, textvariable=self.var_gender,
                                            font=('Verdana', 15))
        self.right_top_gender_label.grid(row=3, column=0)  # 位置设置
        self.right_top_gender_entry.grid(row=3, column=1)
        # 年龄
        self.right_top_gender_label = Label(self.frame_left_top, text="年龄：", font=('Verdana', 15))
        self.right_top_gender_entry = Entry(self.frame_left_top, textvariable=self.var_age,
                                            font=('Verdana', 15))
        self.right_top_gender_label.grid(row=4, column=0)  # 位置设置
        self.right_top_gender_entry.grid(row=4, column=1)

        self.right_top_gender_label = Label(self.frame_left_top, text="宿舍楼号：", font=('Verdana', 15))
        self.right_top_gender_entry = Entry(self.frame_left_top, textvariable=self.var_bno,
                                            font=('Verdana', 15))
        self.right_top_gender_label.grid(row=5, column=0)  # 位置设置
        self.right_top_gender_entry.grid(row=5, column=1)

        # 定义右上方区域
        self.right_top_title = Label(self.frame_right_top, text="操作：", font=('Verdana', 20))

        self.tree.bind('<Button-1>', self.click)  # 左键获取位置
        self.right_top_button1 = ttk.Button(self.frame_right_top, text='新建管理员信息', width=20, command=self.new_row)
        self.right_top_button2 = ttk.Button(self.frame_right_top, text='更新管理员信息', width=20, command=self.updata_row)
        self.right_top_button3 = ttk.Button(self.frame_right_top, text='删除管理员信息', width=20, command=self.del_row)

        # 定义下方区域
        self.chaxun = StringVar()
        self.right_bottom_gender_entry = Entry(self.frame_bottom, textvariable=self.chaxun, font=('Verdana', 15))
        self.right_bottom_button = ttk.Button(self.frame_bottom, text='姓名查询', width=20, command=self.put_data)
        self.right_bottom_button.grid(row=0, column=0, padx=20, pady=20)  # 位置设置
        self.right_bottom_gender_entry.grid(row=0, column=1)

        # 位置设置
        self.right_top_title.grid(row=1, column=0, pady=10)
        self.right_top_button1.grid(row=2, column=0, padx=20, pady=10)
        self.right_top_button2.grid(row=3, column=0, padx=20, pady=10)
        self.right_top_button3.grid(row=4, column=0, padx=20, pady=10)

        # 整体区域定位
        self.frame_left_top.grid(row=0, column=0, padx=2, pady=5)
        self.frame_right_top.grid(row=0, column=1, padx=30, pady=30)
        self.frame_center.grid(row=1, column=0, columnspan=2, padx=4, pady=5)
        self.frame_bottom.grid(row=2, column=0, columnspan=2)

        self.frame_left_top.grid_propagate(0)
        self.frame_right_top.grid_propagate(0)
        self.frame_center.grid_propagate(0)
        self.frame_bottom.grid_propagate(0)

        self.frame_left_top.tkraise()  # 开始显示主菜单
        self.frame_right_top.tkraise()  # 开始显示主菜单
        self.frame_center.tkraise()  # 开始显示主菜单
        self.frame_bottom.tkraise()  # 开始显示主菜单

        self.window.protocol("WM_DELETE_WINDOW", self.back)  # 捕捉右上角关闭点击
        self.window.mainloop()  # 进入消息循环

    # 将查到的信息放到中间的表格中
    def put_data(self):
        self.delButton()  # 先将表格内的内容全部清空

        db = pymssql.connect('LAPTOP-6IF4V507', 'adminmanage', '123456', '学生宿舍管理系统')
        cursor = db.cursor()  # 使用cursor()方法获取操作游标
        sql = "SELECT * FROM admin where aname = '%s'" % (self.chaxun.get())
        try:
            # 执行SQL语句
            cursor.execute(sql)
            # 获取所有记录列表
            results = cursor.fetchall()

            # 再次进行初始化，进行首行数据的插入
            self.id = []
            self.name = []
            self.gender = []
            self.age = []
            self.bno = []
            # 向表格中插入数据
            for row in results:
                self.id.append(row[0])
                self.name.append(row[1])
                self.gender.append(row[2])
                self.age.append(row[3])
                self.bno.append(row[4])

        except:
            print("Error: unable to fetch data")
            messagebox.showinfo('警告！', '数据库连接失败！')
            db.close()  # 关闭数据库连接

        print("进行数据的插入")
        for i in range(min(len(self.id), len(self.name), len(self.gender), len(self.age))):  # 写入数据
            self.tree.insert('', i, values=(self.id[i], self.name[i], self.gender[i], self.age[i], self.bno[i]))

        for col in self.columns:  # 绑定函数，使表头可排序
            self.tree.heading(col, text=col,
                              command=lambda _col=col: self.tree_sort_column(self.tree, _col, False))

    # 清空表格中的所有信息
    def delButton(self):
        x = self.tree.get_children()
        for item in x:
            self.tree.delete(item)

    def back(self):
        All_admin1(self.window)  # 进入管理员子菜单操作界面

    def click(self, event):
        self.col = self.tree.identify_column(event.x)  # 列
        self.row = self.tree.identify_row(event.y)  # 行

        print(self.col)
        print(self.row)
        self.row_info = self.tree.item(self.row, "values")
        self.var_id.set(self.row_info[0])
        self.id1 = self.var_id.get()
        print(self.id1)
        self.var_name.set(self.row_info[1])
        self.var_gender.set(self.row_info[2])
        self.var_age.set(self.row_info[3])
        self.var_bno.set(self.row_info[4])
        self.right_top_id_entry = Entry(self.frame_left_top, state='disabled', textvariable=self.var_id,
                                        font=('Verdana', 15))

    # 排序的方法
    def tree_sort_column(self, tv, col, reverse):  # Treeview、列名、排列方式
        l = [(tv.set(k, col), k) for k in tv.get_children('')]
        l.sort(reverse=reverse)  # 排序方式
        # rearrange items in sorted positions
        for index, (val, k) in enumerate(l):  # 根据排序后索引移动
            tv.move(k, '', index)
        tv.heading(col, command=lambda: self.tree_sort_column(tv, col, not reverse))  # 重写标题，使之成为再点倒序的标题

    # 插入方法
    def new_row(self):
        res = messagebox.askyesnocancel('警告！', '该用户无权进行增加操作！')

    # 更新数据及表格
    def updata_row(self):
        res = messagebox.askyesnocancel('警告！', '该用户无权进行修改操作！')

    # 删除行
    def del_row(self):
        res = messagebox.askyesnocancel('警告！', '该用户无权进行删除操作！')

class Buy_AdminManage1:
    def __init__(self, parent_window):
        parent_window.destroy()  # 销毁主界面

        self.window = Tk()  # 初始框的声明
        self.window.title('管理员操作界面')
        self.window.geometry("600x700+300+30")  # 初始窗口在屏幕中的位置
        self.frame_left_top = tk.Frame(width=350, height=300)  # 指定框架，在窗口上可以显示，这里指定四个框架
        self.frame_right_top = tk.Frame(width=350, height=300)
        self.frame_center = tk.Frame(width=450, height=250)
        self.frame_bottom = tk.Frame(width=650, height=70)

        self.id1 = 0

        # 定义下方中心列表区域
        self.columns = ("学号", "检测时间", "检测结果")
        self.tree = ttk.Treeview(self.frame_center, show="headings", height=18, columns=self.columns)
        self.vbar = ttk.Scrollbar(self.frame_center, orient=VERTICAL, command=self.tree.yview)
        # 定义树形结构与滚动条
        self.tree.configure(yscrollcommand=self.vbar.set)

        # 表格的标题
        self.tree.column("学号", width=100, anchor='center')  # 表示列,不显示
        self.tree.column("检测时间", width=100, anchor='center')
        self.tree.column("检测结果", width=100, anchor='center')

        # 调用方法获取表格内容插入
        self.tree.grid(row=0, column=0, sticky=NSEW)
        self.vbar.grid(row=0, column=1, sticky=NS)

        self.SNO = []
        self.Stime = []
        self.Sout = []

        # 打开数据库连接
        db = pymssql.connect('LAPTOP-6IF4V507', 'adminmanage', '123456', '学生宿舍管理系统')
        cursor = db.cursor()  # 使用cursor()方法获取操作游标
        sql = "SELECT * FROM test"  # SQL 查询语句
        try:
            # 执行SQL语句
            cursor.execute(sql)
            # 获取所有记录列表
            results = cursor.fetchall()
            for row in results:
                self.SNO.append(row[0])
                self.Stime.append(row[1])
                self.Sout.append(row[2])

        except:
            print("Error: unable to fetch data")
            messagebox.showinfo('警告！', '数据库连接失败！')
        db.close()  # 关闭数据库连接

        print("test***********************")
        for i in range(min(len(self.SNO), len(self.Stime), len(self.Sout))):  # 写入数据
            self.tree.insert('', i, values=(self.SNO[i], self.Stime[i], self.Sout[i]))

        for col in self.columns:  # 绑定函数，使表头可排序
            self.tree.heading(col, text=col,
                              command=lambda _col=col: self.tree_sort_column(self.tree, _col, False))

        # 定义顶部区域
        # 定义左上方区域
        self.top_title = Label(self.frame_left_top, text="核酸检测信息", font=('Verdana', 20))
        self.top_title.grid(row=0, column=0, columnspan=2, sticky=NSEW, padx=50, pady=10)

        self.left_top_frame = tk.Frame(self.frame_left_top)
        self.var_id = StringVar()
        self.var_time = StringVar()
        self.var_out = StringVar()

        self.right_top_id_label = Label(self.frame_left_top, text="学号：            ", font=('Verdana', 15))
        self.right_top_id_entry = Entry(self.frame_left_top, textvariable=self.var_id, font=('Verdana', 15))
        self.right_top_id_label.grid(row=1, column=0)  # 位置设置
        self.right_top_id_entry.grid(row=1, column=1)

        self.right_top_name_label = Label(self.frame_left_top, text="检测时间：          ", font=('Verdana', 15))
        self.right_top_name_entry = Entry(self.frame_left_top, textvariable=self.var_time, font=('Verdana', 15))
        self.right_top_name_label.grid(row=2, column=0)  # 位置设置
        self.right_top_name_entry.grid(row=2, column=1)

        self.right_top_gender_label = Label(self.frame_left_top, text="检测结果：          ", font=('Verdana', 15))
        self.right_top_gender_entry = Entry(self.frame_left_top, textvariable=self.var_out, font=('Verdana', 15))
        self.right_top_gender_label.grid(row=3, column=0)  # 位置设置
        self.right_top_gender_entry.grid(row=3, column=1)

        # 定义右上方区域
        self.right_top_title = Label(self.frame_right_top, text="操作", font=('Verdana', 20))

        self.tree.bind('<Button-1>', self.click)  # 左键获取位置
        self.right_top_button1 = ttk.Button(self.frame_right_top, text='新建核酸信息', width=20, command=self.new_row)
        self.right_top_button2 = ttk.Button(self.frame_right_top, text='更新选中核酸信息', width=20, command=self.updata_row)
        self.right_top_button3 = ttk.Button(self.frame_right_top, text='删除选中核酸信息', width=20, command=self.del_row)

        # 定义下方区域
        self.chaxun = StringVar()
        self.right_bottom_gender_entry = Entry(self.frame_bottom, textvariable=self.chaxun, font=('Verdana', 15))
        self.right_bottom_button = ttk.Button(self.frame_bottom, text='学号查询', width=20, command=self.put_data)
        self.right_bottom_button.grid(row=0, column=0, padx=20, pady=20)  # 位置设置
        self.right_bottom_gender_entry.grid(row=0, column=1)

        # 位置设置
        self.right_top_title.grid(row=1, column=0, pady=10)
        self.right_top_button1.grid(row=2, column=0, padx=20, pady=10)
        self.right_top_button2.grid(row=3, column=0, padx=20, pady=10)
        self.right_top_button3.grid(row=4, column=0, padx=20, pady=10)

        # 整体区域定位
        self.frame_left_top.grid(row=0, column=0, padx=2, pady=5)
        self.frame_right_top.grid(row=0, column=1, padx=30, pady=30)
        self.frame_center.grid(row=1, column=0, columnspan=2, padx=5, pady=5)
        self.frame_bottom.grid(row=2, column=0, columnspan=2)

        self.frame_left_top.grid_propagate(0)
        self.frame_right_top.grid_propagate(0)
        self.frame_center.grid_propagate(0)
        self.frame_bottom.grid_propagate(0)

        self.frame_left_top.tkraise()  # 开始显示主菜单
        self.frame_right_top.tkraise()  # 开始显示主菜单
        self.frame_center.tkraise()  # 开始显示主菜单
        self.frame_bottom.tkraise()  # 开始显示主菜单

        self.window.protocol("WM_DELETE_WINDOW", self.back)  # 捕捉右上角关闭点击
        self.window.mainloop()  # 进入消息循环

    def back(self):
        All_admin1(self.window)  # 进入管理员子菜单操作界面

    # 将查到的信息放到中间的表格中
    def put_data(self):
        self.delButton()  # 先将表格内的内容全部清空

        # print(self.chaxun.get())	# 输入框内的内容
        # 打开数据库连接，准备查找指定的信息
        db = pymssql.connect('LAPTOP-6IF4V507', 'adminmanage', '123456', '学生宿舍管理系统')
        cursor = db.cursor()  # 使用cursor()方法获取操作游标
        sql = "SELECT * FROM test where sno = '%s'" % (self.chaxun.get())
        try:
            # 执行SQL语句
            cursor.execute(sql)
            # 获取所有记录列表
            results = cursor.fetchall()

            # 再次进行初始化，进行首行数据的插入
            self.SNO = []
            self.Stime = []
            self.Sout = []

            # 向表格中插入数据
            for row in results:
                self.SNO.append(row[0])
                self.Stime.append(row[1])
                self.Sout.append(row[2])

        except:
            print("Error: unable to fetch data")
            messagebox.showinfo('警告！', '数据库连接失败！')
            db.close()  # 关闭数据库连接

        for i in range(min(len(self.SNO), len(self.Stime), len(self.Sout))):  # 写入数据
            self.tree.insert('', i, values=(self.SNO[i], self.Stime[i], self.Sout[i]))

        for col in self.columns:  # 绑定函数，使表头可排序
            self.tree.heading(col, text=col,
                              command=lambda _col=col: self.tree_sort_column(self.tree, _col, False))

    # 清空表格中的所有信息
    def delButton(self):
        x = self.tree.get_children()
        for item in x:
            self.tree.delete(item)

    def click(self, event):
        self.col = self.tree.identify_column(event.x)  # 列
        self.row = self.tree.identify_row(event.y)  # 行

        print(self.col)
        print(self.row)
        self.row_info = self.tree.item(self.row, "values")
        self.var_id.set(self.row_info[0])
        self.id1 = self.var_id.get()
        print(self.id1)
        self.var_time.set(self.row_info[1])
        self.var_out.set(self.row_info[2])

        self.right_top_id_entry = Entry(self.frame_left_top, state='disabled', textvariable=self.var_id,
                                        font=('Verdana', 15))

        print('')

    def tree_sort_column(self, tv, col, reverse):  # Treeview、列名、排列方式
        l = [(tv.set(k, col), k) for k in tv.get_children('')]
        l.sort(reverse=reverse)  # 排序方式
        # rearrange items in sorted positions
        for index, (val, k) in enumerate(l):  # 根据排序后索引移动
            tv.move(k, '', index)
        tv.heading(col, command=lambda: self.tree_sort_column(tv, col, not reverse))  # 重写标题，使之成为再点倒序的标题

    def new_row(self):
        res = messagebox.askyesnocancel('警告！', '无操作权限！')

    def updata_row(self):
        res = messagebox.askyesnocancel('警告！', '无操作权限！')


    # 删除行
    def del_row(self):
        res = messagebox.askyesnocancel('警告！', '无操作权限！')
